Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DItem

    Public Shared Function GetItem(ByVal ItemID As Integer) As DataSet
        Dim strSql As String = ""

        Try
            '@20071012 : Changes are made in select query related to Margin Textbox
            '<< start
            'strSql = "SELECT ItemID,SeqItemID,ItemShortName,ItemName,ItemType,ItemCatID,ItemForm,Grade,mfctrID,companyID,GenericName,ItemUnit," & _
            '            "PurPack,ItemsPerPack,PurUnit,LocationID,SellLoose,SellUnit,LastPurPrice,PurPrice,SalePrice," & _
            '            "Barcode,MaxDiscount,OpeningStock,MinQty,MaxQty,MinReorderLevel,MaxReorderQty,Action1,Action2," & _
            '            "Action3,VendorID1,VendorID2,VendorID3,ScheduleDrug,Remark " & _
            '            "FROM mPItem " & _
            '            "WHERE ItemID = " & ItemID & " and Status = 'A'"

            strSql = "SELECT ItemID,SeqItemID,ItemShortName,ItemName,ItemType,ItemCatID,ItemForm,Grade,mfctrID,companyID,GenericName,ItemUnit," & _
                        "PurPack,ItemsPerPack,PurUnit,LocationID,SellLoose,SellUnit,LastPurPrice,PurPrice,SalePrice," & _
                        "Barcode,MaxDiscount,Margincomm,MinQty,MaxQty,MinReorderLevel,MaxReorderQty,Action1,Action2," & _
                        "Action3,VendorID1,VendorID2,VendorID3,ScheduleDrug,Remark,ELStock " & _
                        "FROM mPItem " & _
                        "WHERE ItemID = " & ItemID & " and Status = 'A'"
            '>> END
            Return DbHelper.ExecuteDataSet(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItem", strSql)
            Return Nothing
        End Try

    End Function
    Public Shared Function GetItem(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable

        Dim StrSql As String = ""
        Try
            ' from mLocation.LocationID 
            StrSql = "SELECT mpItem.ItemID,SeqItemID AS ItemCode,ItemName AS Name,mfctrShortName AS Company,CurrentStock AS Stock," & _
            " tStock.PurPack  + '-'+ CAST(ItemsPerPack as varchar) + ItemForm as Pack ," & _
            "CAST(MONTH(tStock.ExpDate)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(tStock.ExpDate)AS VARCHAR),2) AS EXPDATE," & _
            "tStock.PurchasePrice,LocationCode,ScheduleDrug,SchemeItemQty" & _
            " FROM mpItem, mManufacturer, tStockSummary,tStock,mLocation " & _
            " WHERE mManufacturer.mfctrID = mpItem.mfctrID AND tStockSummary.ItemID = mpItem.ItemID AND mpItem.ItemID=" & ItemID & _
            " AND tStock.ItemID = mpItem.ItemID AND mLocation.LocationID =tStock.LocationID AND tStock.CLRcode= '" & CLRcode & "'"
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemDetail", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetSubstitueItem(ByVal ItemID As Integer) As DataSet
        Dim strSql As String = ""
        Try
            strSql = "SELECT ItemName,ItemID FROM mpItem  WHERE ItemID IN(" & _
            "SELECT substituteItemID FROM tsubstitute  WHERE ItemID = " & ItemID & ")"
            Return DbHelper.ExecuteDataSet(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetSubstitueItem", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetTax(ByVal ItemID As Integer) As DataSet
        Dim strSql As String = ""
        Try
            strSql = " SELECT m.TaxName, m.Tax_Type, t.Amount, t.TaxID, t.Inclusive, m.ApplyTo " & _
                     " FROM tItemTax t, mtax m WHERE " & _
                     " t.TaxID = m.TaxID  and " & _
                     " ItemID =" & ItemID
            Return DbHelper.ExecuteDataSet(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetTax", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function Save(ByRef Item As CItem, ByVal SaveUpdate As String, ByRef substitute() As Integer, ByRef tax() As CTax) As Integer
        Dim dbConnection As New dbAccess
        Dim intItemID As Integer
        Dim blnSubstitute As Boolean = False
        Dim blnTax As Boolean = False
        Dim intResult As Integer
        Try
            dbConnection.BeginTransaction()
            intItemID = SaveItem(Item, SaveUpdate, dbConnection)
            intResult = intItemID
            If intResult = 0 Then Exit Function
            If SaveUpdate = "S" Then 'Chandrakant
                intResult = SaveStockSummary(Item, SaveUpdate, dbConnection)
            Else
                intResult = 1
            End If
            If intResult > 0 Then
                If substitute(0) = 0 And SaveUpdate = "S" Then : blnSubstitute = True
                Else : blnSubstitute = SaveSubstitute(intItemID, substitute, SaveUpdate, dbConnection)
                End If
                If blnSubstitute = False Then
                    intResult = 0
                    Exit Function
                End If
                If tax.Length = 0 Then
                    blnTax = True
                Else
                    blnTax = SaveTax(intItemID, tax, SaveUpdate, dbConnection)
                End If
                If blnTax = False Then intResult = 0
            End If

        Catch ex As Exception
            intResult = 0
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "Save")
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                Save = 0
            Else
                dbConnection.CommitTransaction()
                Save = intItemID
            End If
            dbConnection.closeSqlcon()
        End Try

    End Function

    Public Shared Function SaveStockSummary(ByRef Item As CItem, ByVal SaveUpdate As String, ByRef dbcon As dbAccess) As Integer
        Dim sql As String = ""
        Try
            If SaveUpdate = "S" Then
                Dim param(13) As SqlParameter
                sql = "INSERT INTO tStockSummary(StockSumID,ItemID,OpeningStock,CurrentStock,SchemStock,GodownStock," & _
                      "BrekageStock,ExpiredStock,LastSaleBill,mfctrID,LastPurchaseInvoice,LU,LD,FinYrID)" & _
                      "VALUES(@StockSumID,@ItemID,@OpeningStock,@CurrentStock,@SchemStock,@GodownStock," & _
                      "@BrekageStock,@ExpiredStock,@LastSaleBill,@mfctrID,@LastPurchaseInvoice,@LU,@LD,@FinYrID)"
                param(0) = New SqlParameter("@StockSumID", DGeneral.GetSeqNum("STOCKSUM"))
                param(1) = New SqlParameter("@ItemID", Item.ItemID)
                param(2) = New SqlParameter("@OpeningStock", IIf(Item.OpeningStock = Nothing, "0", Item.OpeningStock))
                param(3) = New SqlParameter("@CurrentStock", Item.OpeningStock)
                param(4) = New SqlParameter("@SchemStock", 0)
                param(5) = New SqlParameter("@GodownStock", 0)
                param(6) = New SqlParameter("@BrekageStock", 0)
                param(7) = New SqlParameter("@ExpiredStock", 0)
                param(8) = New SqlParameter("@LastSaleBill", 0)
                param(9) = New SqlParameter("@mfctrID", Item.ManufacturedID)
                param(10) = New SqlParameter("@LastPurchaseInvoice", 0)
                param(11) = New SqlParameter("@LU", CCommon.GetUserID)
                param(12) = New SqlParameter("@LD", Date.Now)
                param(13) = New SqlParameter("@FinYrID", CCommon.GetFinYrID)

                Return dbcon.ExecuteNonQuery(sql, param)
            Else
                Dim param(12) As SqlParameter
                sql = "UPDATE tStockSummary set OpeningStock=@OpeningStock," & _
                      "CurrentStock=@CurrentStock,SchemStock=@SchemStock,GodownStock=@GodownStock,BrekageStock=@BrekageStock," & _
                      "ExpiredStock=@ExpiredStock,LastSaleBill=@LastSaleBill,mfctrID=@mfctrID,LastPurchaseInvoice=@LastPurchaseInvoice," & _
                      "LU=@LU,LD=@LD,FinYrID=@FinYrID WHERE ItemID=@ItemID"

                param(0) = New SqlParameter("@OpeningStock", IIf(Item.OpeningStock = Nothing, "0", Item.OpeningStock))
                param(1) = New SqlParameter("@CurrentStock", Item.OpeningStock)
                param(2) = New SqlParameter("@SchemStock", 0)
                param(3) = New SqlParameter("@GodownStock", 0)
                param(4) = New SqlParameter("@BrekageStock", 0)
                param(5) = New SqlParameter("@ExpiredStock", 0)
                param(6) = New SqlParameter("@LastSaleBill", 0)
                param(7) = New SqlParameter("@mfctrID", Item.ManufacturedID)
                param(8) = New SqlParameter("@LastPurchaseInvoice", 0)
                param(9) = New SqlParameter("@LU", CCommon.GetUserID)
                param(10) = New SqlParameter("@LD", Date.Now)
                param(11) = New SqlParameter("@FinYrID", CCommon.GetFinYrID)
                param(12) = New SqlParameter("@ItemID", Item.ItemID)
                Return dbcon.ExecuteNonQuery(sql, param)
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "Save")
            Return 0
        End Try
    End Function


    Public Shared Function SaveSubstitute(ByVal ItemID As Integer, ByRef substitute() As Integer, ByVal saveupdate As String, ByRef dbcon As dbAccess) As Boolean
        Dim sql As String = ""
        Dim delsql As String = ""
        Dim param(7) As SqlParameter
        Dim intcount As Integer
        Dim i As Integer
        Try

            If saveupdate <> "S" Then
                delsql = "DELETE FROM tSubstitute WHERE ItemID=" & ItemID
                dbcon.ExecuteNonQuery(delsql)
                If substitute(0) = 0 Then
                    Return True
                Else
                    Return SaveSubstitute(ItemID, substitute, "S", dbcon)
                End If
            End If
            For intcount = 0 To substitute.Length - 1
                If substitute(intcount) <> 0 Then
                    sql = "INSERT INTO tSubstitute(SubstituteID,ItemID,SubstituteItemID,Status,CU,CD,LU,LD)" & _
                            "VALUES(@SubstituteID,@ItemID,@SubstituteItemID,@Status,@CU,@CD,@LU,@LD)"
                    param(0) = New SqlParameter("@SubstituteID", DGeneral.GetSeqNum("SUBSTITUTE"))
                    param(1) = New SqlParameter("@ItemID", ItemID)
                    param(2) = New SqlParameter("@SubstituteItemID", substitute(intcount))
                    param(3) = New SqlParameter("@Status", "A")
                    param(4) = New SqlParameter("@CU", CCommon.GetUserID)
                    param(5) = New SqlParameter("@CD", Date.Now)
                    param(6) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(7) = New SqlParameter("@LD", Date.Now)

                    i = i + dbcon.ExecuteNonQuery(sql, param)
                End If
            Next

            Return True
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveSubstitute")
            Return False
        End Try
    End Function

    Public Shared Function SaveTax(ByVal ItemID As Integer, ByRef tax() As CTax, ByVal SaveUpdate As String, ByRef dbcon As dbAccess) As Boolean
        Dim sql As String = ""
        Dim delsql As String = ""
        Dim param(2) As SqlParameter
        Dim intCount As Integer
        Dim i As Integer
        Try
            If SaveUpdate <> "S" Then
                delsql = "DELETE FROM tItemTax WHERE ItemID=" & ItemID
                dbcon.ExecuteNonQuery(delsql)
                If tax(0).TaxID = 0 Then
                    Return True
                Else
                    Return SaveTax(ItemID, tax, "S", dbcon)
                End If
            End If

            For intCount = 0 To tax.Length - 1
                If Not IsNothing(tax(intCount)) Then
                    sql = "INSERT INTO tItemTax(ItemID,TaxID,Amount)" & _
                               "VALUES(@ItemID,@TaxID,@Amount)"
                    param(0) = New SqlParameter("@TaxID", tax(intCount).TaxID)
                    param(1) = New SqlParameter("@ItemID", ItemID)
                    'param(2) = New SqlParameter("@Inclusive", IIf(tax(intCount).TaxInclusive = Nothing, "", tax(intCount).TaxInclusive))
                    param(2) = New SqlParameter("@Amount", tax(intCount).TaxAmount)
                    i = i + dbcon.ExecuteNonQuery(sql, param)
                End If
            Next
            Return True
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveTax")
            Return False
        End Try
    End Function
    'Code start(For Tax)                     ...19/09/2007
    Public Shared Function SaveTax(ByVal ItemID As Integer, ByRef tax() As CTax, ByVal SaveUpdate As String) As Integer
        Dim sql As String = ""
        Dim delsql As String = ""
        Dim param(3) As SqlParameter
        Dim intCount As Integer
        Dim i As Integer
        Try
            If SaveUpdate <> "S" Then
                delsql = "DELETE FROM tItemTax WHERE ItemID=" & ItemID
                DbHelper.ExecuteNonQuery(delsql)
                If tax(0).TaxID = 0 Then
                    Return 1
                Else
                    Return SaveTax(ItemID, tax, "S")
                End If
            End If

            For intCount = 0 To tax.Length - 1
                If Not IsNothing(tax(intCount)) Then
                    If tax(intCount).TaxAmount <> 0 Then
                        sql = "INSERT INTO tItemTax(ItemID,TaxID,Inclusive,Amount)" & _
                                   "VALUES(@ItemID,@TaxID,@Inclusive,@Amount)"
                        param(0) = New SqlParameter("@TaxID", tax(intCount).TaxID)
                        param(1) = New SqlParameter("@ItemID", ItemID)
                        param(2) = New SqlParameter("@Inclusive", tax(intCount).TaxInclusive)
                        param(3) = New SqlParameter("@Amount", tax(intCount).TaxAmount)
                        i = i + DbHelper.ExecuteNonQuery(sql, param)
                    End If
                End If
            Next
            Return 1
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveTax", sql)
            Return False
        End Try
    End Function

    'Code End
    Public Shared Function SaveItem(ByRef Item As CItem, ByVal SaveUpdate As String, ByRef dbcon As dbAccess) As Integer
        Dim sql As String = ""
        Dim param(39) As SqlParameter

        Try
            If SaveUpdate = "S" Then
                '@20071012 : Changes are made in insert query related to Margin
                '<<Start
                'sql = "INSERT INTO mPItem(ItemID,SeqItemID,ItemName,ItemShortName,ItemType,ItemCatID,ItemForm,Grade,mfctrID," & _
                '        "CompanyID,GenericName,ItemUnit,PurPack,ItemsPerPack,PurUnit,LocationID,SellLoose," & _
                '        "SellUnit,LastPurPrice,PurPrice,SalePrice,Barcode,MaxDiscount,OpeningStock,MinQty,MaxQty," & _
                '        "MinReorderLevel,MaxReorderQty,Action1,Action2,Action3,VendorID1,VendorID2,VendorID3," & _
                '        "ScheduleDrug,Remark,Status,LU,LD)" & _
                '        "VALUES(@ItemID,@SeqItemID,@ItemName,@ItemShortName,@ItemType,@ItemCatID,@ItemForm,@Grade,@mfctrID," & _
                '        "@CompanyID,@GenericName,@ItemUnit,@PurPack,@ItemsPerPack,@PurUnit,@LocationID,@SellLoose," & _
                '        "@sellUnit,@LastPurPrice,@PurPrice,@SalePrice,@Barcode,@MaxDiscount,@OpeningStock,@MinQty,@MaxQty," & _
                '        "@MinReorderLevel,@MaxReorderQty,@Action1,@Action2,@Action3,@VendorID1,@VendorID2,@VendorID3," & _
                '        "@ScheduleDrug,@Remark,@Status,@LU,@LD)"

                sql = "INSERT INTO mPItem(ItemID,SeqItemID,ItemName,ItemShortName,ItemType,ItemCatID,ItemForm,Grade,mfctrID," & _
                        "CompanyID,GenericName,ItemUnit,PurPack,ItemsPerPack,PurUnit,LocationID,SellLoose," & _
                        "SellUnit,LastPurPrice,PurPrice,SalePrice,Barcode,MaxDiscount,MarginComm,MinQty,MaxQty," & _
                        "MinReorderLevel,MaxReorderQty,Action1,Action2,Action3,VendorID1,VendorID2,VendorID3," & _
                        "ScheduleDrug,Remark,Status,LU,LD,ELStock)" & _
                        "VALUES(@ItemID,@SeqItemID,@ItemName,@ItemShortName,@ItemType,@ItemCatID,@ItemForm,@Grade,@mfctrID," & _
                        "@CompanyID,@GenericName,@ItemUnit,@PurPack,@ItemsPerPack,@PurUnit,@LocationID,@SellLoose," & _
                        "@sellUnit,@LastPurPrice,@PurPrice,@SalePrice,@Barcode,@MaxDiscount,@Margincm,@MinQty,@MaxQty," & _
                        "@MinReorderLevel,@MaxReorderQty,@Action1,@Action2,@Action3,@VendorID1,@VendorID2,@VendorID3," & _
                        "@ScheduleDrug,@Remark,@Status,@LU,@LD,@ELS)"
                'END >>
                Item.ItemID = DGeneral.GetSeqNum("ITEM")
                param(0) = New SqlParameter("@ItemID", Item.ItemID)
            Else
                '@20071012 : Changes are made in insert query related to Margin
                '<<Start
                'sql = "Update mPItem set  SeqItemID=@SeqItemID,ItemName = @ItemName, ItemShortName=@ItemShortName," & _
                '      "ItemType = @ItemType,ItemCatID = @ItemCatID,ItemForm = @ItemForm,Grade= @Grade," & _
                '      "mfctrID = @mfctrID,CompanyID=@CompanyID,GenericName = @GenericName," & _
                '      "ItemUnit= @ItemUnit,PurPack = @PurPack,ItemsPerPack = @ItemsPerPack,PurUnit = @PurUnit," & _
                '      "LocationID = @LocationID,SellLoose = @SellLoose,SellUnit = @SellUnit," & _
                '      "LastPurPrice = @LastPurPrice,PurPrice = @PurPrice,SalePrice = @SalePrice," & _
                '      "Barcode  = @Barcode,MaxDiscount = @MaxDiscount,OpeningStock = @OpeningStock," & _
                '      "MinQty = @MinQty,MaxQty = @MaxQty,MinReorderLevel = @MinReorderLevel," & _
                '      "MaxReorderQty= @MaxReorderQty,Action1 = @Action1,Action2 = @Action2," & _
                '      "Action3 = @Action3,VendorID1 = @VendorID1,VendorID2 = @VendorID2," & _
                '      "VendorID3 = @VendorID3,ScheduleDrug = @ScheduleDrug," & _
                '      "Remark = @Remark,Status = @Status,LU = @LU,LD =@LD where itemID = @ItemID "

                sql = "Update mPItem set  SeqItemID=@SeqItemID,ItemName = @ItemName, ItemShortName=@ItemShortName," & _
                      "ItemType = @ItemType,ItemCatID = @ItemCatID,ItemForm = @ItemForm,Grade= @Grade," & _
                      "mfctrID = @mfctrID,CompanyID=@CompanyID,GenericName = @GenericName," & _
                      "ItemUnit= @ItemUnit,PurPack = @PurPack,ItemsPerPack = @ItemsPerPack,PurUnit = @PurUnit," & _
                      "LocationID = @LocationID,SellLoose = @SellLoose,SellUnit = @SellUnit," & _
                      "LastPurPrice = @LastPurPrice,PurPrice = @PurPrice,SalePrice = @SalePrice," & _
                      "Barcode  = @Barcode,MaxDiscount = @MaxDiscount,MarginComm = @Margincm," & _
                      "MinQty = @MinQty,MaxQty = @MaxQty,MinReorderLevel = @MinReorderLevel," & _
                      "MaxReorderQty= @MaxReorderQty,Action1 = @Action1,Action2 = @Action2," & _
                      "Action3 = @Action3,VendorID1 = @VendorID1,VendorID2 = @VendorID2," & _
                      "VendorID3 = @VendorID3,ScheduleDrug = @ScheduleDrug," & _
                      "Remark = @Remark,Status = @Status,LU = @LU,LD =@LD ,ELStock = @ELS where itemID = @ItemID "
                ' >> END
                param(0) = New SqlParameter("@ItemID", Item.ItemID)
            End If

            param(1) = New SqlParameter("@SeqItemID", DGeneral.GetSeqNum("ITEM") & Item.ItemID.ToString())
            param(2) = New SqlParameter("@ItemName", Item.ItemName)
            param(3) = New SqlParameter("@ItemShortName", Item.ShortName)
            param(4) = New SqlParameter("@ItemType", Item.ItemType)
            param(5) = New SqlParameter("@ItemCatID", IIf(IsNothing(Item.ItemCategoryID), "0", Item.ItemCategoryID))
            param(6) = New SqlParameter("@ItemForm", Item.ItemForm)
            param(7) = New SqlParameter("@Grade", IIf(IsNothing(Item.ItemGrade), "", Item.ItemGrade))
            param(8) = New SqlParameter("@mfctrID", Item.ManufacturedID)
            param(9) = New SqlParameter("@CompanyID", Item.CompanyId)
            param(10) = New SqlParameter("@GenericName", Item.GenericName)
            param(11) = New SqlParameter("@ItemUnit", Item.ItemUnit)
            param(12) = New SqlParameter("@PurPack", Item.PurPackType)
            param(13) = New SqlParameter("@ItemsPerPack", Item.ItemsPerPack)
            param(14) = New SqlParameter("@PurUnit", Item.PurchaseUnit)
            param(15) = New SqlParameter("@LocationID", Item.LocationID)
            param(16) = New SqlParameter("@SellLoose", IIf(Item.SellInLoose = True, "1", "0"))
            param(17) = New SqlParameter("@SellUnit", Item.SellUnit)
            param(18) = New SqlParameter("@LastPurPrice", IIf(Item.LastPurchasePrice = Nothing, "0.00", Item.LastPurchasePrice))
            param(19) = New SqlParameter("@PurPrice", IIf(Item.LastPurchasePrice = Nothing, "0.00", Item.PurchasePrice))
            param(20) = New SqlParameter("@SalePrice", IIf(Item.SalePrice = Nothing, "0.00", Item.SalePrice))
            param(21) = New SqlParameter("@Barcode", Item.BarCode)
            param(22) = New SqlParameter("@MaxDiscount", IIf(Item.MaxDiscount = Nothing, "0.00", Item.MaxDiscount))
            '@20071012 
            '<< Start
            'param(23) = New SqlParameter("@OpeningStock", IIf(Item.OpeningStock = Nothing, "0", Item.OpeningStock))
            param(23) = New SqlParameter("@Margincm", IIf(Item.Margin = Nothing, "0.00", Item.Margin))
            '>> END 
            param(24) = New SqlParameter("@MinQty", IIf(Item.MinQuantity = Nothing, "0", Item.MinQuantity))
            param(25) = New SqlParameter("@MaxQty", IIf(Item.MaxQuantity = Nothing, "0", Item.MaxQuantity))
            param(26) = New SqlParameter("@MinReorderLevel", IIf(Item.MinReOrderLevel = Nothing, "0", Item.MinReOrderLevel))
            param(27) = New SqlParameter("@MaxReorderQty", IIf(Item.MaxReOrderQty = Nothing, "0", Item.MaxReOrderQty))
            param(28) = New SqlParameter("@Action1", IIf(Item.Action1 = Nothing, "", Item.Action1))
            param(29) = New SqlParameter("@Action2", IIf(Item.Action2 = Nothing, "", Item.Action2))
            param(30) = New SqlParameter("@Action3", IIf(Item.Action3 = Nothing, "", Item.Action3))
            param(31) = New SqlParameter("@VendorID1", IIf(Item.VendorID1 = Nothing, "0", Item.VendorID1))
            param(32) = New SqlParameter("@VendorID2", IIf(Item.VendorID2 = Nothing, "0", Item.VendorID2))
            param(33) = New SqlParameter("@VendorID3", IIf(Item.VendorID3 = Nothing, "0", Item.VendorID3))
            param(34) = New SqlParameter("@ScheduleDrug", IIf(IsNothing(Item.Scheduletype), "", Item.Scheduletype))
            param(35) = New SqlParameter("@Remark", IIf(Item.Remark = Nothing, "", Item.Remark))
            param(36) = New SqlParameter("@Status", "A")
            param(37) = New SqlParameter("@LU", CCommon.GetUserID())
            param(38) = New SqlParameter("@LD", Date.Now())
            param(39) = New SqlParameter("@ELS", IIf(Item.Emergency = Nothing, "0", Item.Emergency))
            If dbcon.ExecuteNonQuery(sql, param) > 0 Then
                Return Item.ItemID
            Else
                Return 0
            End If

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveItem")
            Return 0
        End Try

    End Function


    Public Shared Function GetItem() As DataSet
        Return DbHelper.ExecuteDataSet("SELECT '0' as ItemID, '' as ItemName FROM mPItem UNION SELECT ItemID,ItemName FROM mPItem")
    End Function
    'Code End 

    Public Shared Function GetItems() As DataTable

        'Returns The ItemList used for Sale Item List

        Dim StrSql As String = ""

        Try
            StrSql = "SELECT ItemID,ItemName,seqItemID " & _
                "FROM mpItem Order by ItemName"
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItems", StrSql)
            Return Nothing
        End Try
    End Function
    'test
    Public Shared Function GetItemNames() As DataTable
        Dim StrSql As String = ""

        Try
            StrSql = " SELECT '0' as ItemID, '' as ItemName,'' as seqItemID" & _
                     " FROM mPItem UNION SELECT ItemID,ItemName,seqItemID FROM mpItem Order by ItemName"
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemNames", StrSql)
            Return Nothing
        End Try

    End Function

    Public Shared Function GetItemsPI(ByVal VendorID As Integer) As DataTable
        Dim StrSql As String
        Try
            'Comment Following Code and Write New one

            ''StrSql = "SELECT   tStock.ItemID, ItemName, mpItem.PurPack, SalePrice, ItemUnit, ItemsPerPack, mpItem.PurUnit" & _
            ''        "FROM(mpItem, tStock, MMISCCD)" & _
            ''        "WHERE mPItem.ItemForm=MMISCCD.CodeID AND MMISCCD.CodeType='ITEMFORM' AND mpItem.itemID = tStock.itemID" & _
            ''        "AND tStock.itemID IN (SELECT itemID from tRelation where VendorID =" & VendorID & _
            ''        ") AND mPItem.Status = 'A' GROUP BY  tStock.ItemID,ItemName,CodeDesc,mpItem.PurPack, mpItem.PurUnit,SalePrice,ItemUnit, ItemsPerPack"

            StrSql = " SELECT tStock.ItemID, ItemName " & _
                     " FROM mpItem, tStock, MMISCCD " & _
                     " WHERE mPItem.ItemForm = MMISCCD.CodeID AND MMISCCD.CodeType = 'ITEMFORM' AND mpItem.itemID = tStock.itemID " & _
                     " AND tStock.itemID IN (SELECT itemID FROM tRelation WHERE VendorID = " & VendorID & _
                     " AND mPItem.Status = 'A' GROUP BY  tStock.ItemID, ItemName, CodeDesc, mpItem.PurPack, mpItem.PurUnit, SalePrice, ItemUnit, ItemsPerPack"
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemsPI")
            Return Nothing
        End Try

    End Function

    Public Shared Function GetItemsPI() As DataTable
        Try
            Dim StrSql As String
            'Comment Following Code and Write New one

            ''StrSql = "SELECT mpItem.ItemID,SeqItemID AS ItemCode,ItemName AS Name,mfctrShortName AS Company," & _
            ''           " PurPack  + ' of '+ CAST(ItemsPerPack as varchar) +' '+  ItemForm as Pack " & _
            ''           "FROM mpItem, mManufacturer " & _
            ''           " WHERE mManufacturer.mfctrID = mpItem.mfctrID ORDER BY ItemName"

            StrSql = " SELECT ItemID, ItemName AS Name " & _
                     " FROM mpItem  ORDER BY ItemName" ', mManufacturer " & _
            '" WHERE mManufacturer.mfctrID = mpItem.mfctrID ORDER BY ItemName"
            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemsPI")
            Return Nothing
        End Try

    End Function


    Public Shared Function GetItemsDetail() As DataTable

        'Returns The ItemList used for Sale Item List

        Dim StrSql As String = ""

        Try
            StrSql = "SELECT tStock.ItemID,ItemName,purUnit, purPrice, CodeDesc,mfctrName as Manufacturer ,SUM(Quantity) as Stock " & _
                "FROM  mpItem,tStock,MMISCCD, mManufacturer " & _
                "WHERE mPItem.ItemForm=MMISCCD.CodeID AND MMISCCD.CodeType='ITEMFORM' " & _
                "AND  mPItem.mfctrID=mManufacturer.mfctrID AND  mPItem.Status = 'A' " & _
                "GROUP BY  tStock.ItemID,ItemName,CodeDesc,mfctrName "

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItems", StrSql)
            Return Nothing
        End Try

    End Function

    Public Shared Function GetItemsDetail(ByVal vendorID As Integer) As DataTable
        Dim StrSql As String = ""
        Try

            'StrSql = "SELECT tStock.itemID, ItemName,CodeDesc,mfctrName as Manufacturer ,SUM(Quantity) as Stock " & _
            '    "FROM  mpItem,tStock,MMISCCD, mManufacturer " & _
            '    "WHERE mPItem.ItemForm=MMISCCD.CodeID AND MMISCCD.CodeType='ITEMFORM' AND mpItem.itemID = tStock.itemID" & _
            '    " AND  mPItem.mfctrID=mManufacturer.mfctrID AND tStock.itemID IN (SELECT itemID from tRelation where VendorID = " & vendorID & _
            '    ") AND mPItem.Status = 'A' GROUP BY  tStock.ItemID,ItemName,CodeDesc,mfctrName "

            StrSql = "SELECT mpItem.ItemID,ItemName, SUM(Quantity), mpItem.maxReorderQty, mpItem.purUnit, mpItem.purPrice " & _
                     " FROM mpItem, tStock, tRelation" & _
                     " WHERE tRelation.ItemID = mpItem.ItemID And tStock.ItemID = mpItem.ItemID And vendorID = " & vendorID & _
                     " GROUP BY  mpItem.ItemID, ItemName, mpItem.purUnit, mpItem.purPrice, maxReorderQty "

            '"AND mPItem.ItemID=tStock.ItemID " & _
            'StrSql = "SELECT * FROM MMISCCD"   

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemsDetail", StrSql)
            Return Nothing
        End Try

    End Function

    Public Shared Function ItemCodeExists(ByVal strItemID As String) As Integer
        Dim strsql As String = ""
        Try
            strsql = "SELECT ItemID FROM mpItem WHERE  SeqItemID = '" & strItemID & "'"
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "ItemCodeExists", strsql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetSubstituteStock(ByVal ItemID As String, ByVal intColumnIndex As Integer, ByRef intSubstitutes As Integer) As SqlDataReader
        'Chandrakant 20070206
        Dim strSql As String = ""
        Dim strCountSql As String = ""
        Try
            strSql = "SELECT mpItem.ItemID,SeqItemID AS Code,ItemName AS Name,mfctrShortName AS Company,CurrentStock AS Stock,  " & _
            "LocationCode,mpItem.minreorderlevel as ReorderQty ,ScheduleDrug as Schedule,ELStock as EmergStock   " & _
            "FROM mpItem, mManufacturer, tStockSummary, mLocation  " & _
            "WHERE  mpItem.ItemID IN " & _
            "(SELECT SubstituteItemID FROM tSubstitute WHERE ItemID=" & ItemID & ") " & _
            "AND mpItem.ItemID=tStockSummary.ItemID AND tStockSummary.CurrentStock > 0 " & _
            "AND mManufacturer.mfctrID = mpItem.mfctrID AND mLocation.LocationID=mpItem.LocationID ORDER BY " & IIf(intColumnIndex = 1, "Code", "Name")

            strCountSql = "SELECT count(mpItem.ItemID) " & _
            "FROM mpItem, tStockSummary " & _
            "WHERE  mpItem.ItemID IN " & _
            "(SELECT SubstituteItemID FROM tSubstitute WHERE ItemID=" & ItemID & ") " & _
            "AND mpItem.ItemID=tStockSummary.ItemID AND tStockSummary.CurrentStock > 0 "

            'strSql = "SELECT mpItem.ItemID,SeqItemID AS ItemCode,ItemName AS Name,mfctrShortName AS Company,CurrentStock AS Stock, " & _
            '            " PurPack  + ' of '+ CAST(ItemsPerPack as varchar) +' '+  ItemForm as Pack " & _
            '            "FROM mpItem, mManufacturer, tStockSummary " & _
            '            "WHERE mpItem.ItemID IN " & _
            '            "(SELECT SubstituteItemID FROM tSubstitute WHERE ItemID=" & ItemID & _
            '            ") AND mpItem.ItemID=tStockSummary.ItemID AND tStockSummary.CurrentStock > 0  " & _
            '            "AND mManufacturer.mfctrID = mpItem.mfctrID "
            intSubstitutes = DbHelper.ExecuteScalar(strCountSql)
            'If intSubstitutes > 0 Then
            Return DbHelper.ExecuteDataReader(strSql)
            'Else
            'Return Nothing
            'End If

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetSubstituteStock", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function HasSubstitute(ByVal ItemID As String, ByVal intColumnIndex As Integer) As Boolean
        'Chandrakant 20070206
        Dim strSql As String = ""
        Try

            strSql = "SELECT mpItem.ItemID " & _
            "FROM mpItem, mManufacturer, tStockSummary, mLocation  " & _
            "WHERE  mpItem.ItemID IN " & _
            "(SELECT SubstituteItemID FROM tSubstitute WHERE ItemID=" & ItemID & ") " & _
            "AND mpItem.ItemID=tStockSummary.ItemID AND tStockSummary.CurrentStock > 0 " & _
            "AND mManufacturer.mfctrID = mpItem.mfctrID AND mLocation.LocationID=mpItem.LocationID "

            Dim SubstituteID As Long = DbHelper.ExecuteScalar(strSql)
            If IsNothing(SubstituteID) OrElse SubstituteID = 0 Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "HasSubstitute", strSql)
            Return False
        End Try

    End Function

    Public Shared Function GetItemList(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer) As SqlDataReader
        'Returns The ItemList used for Sale Item List
        Dim StrSql As String = ""
        Try

            'StrSql = "SELECT  mpItem.ItemID,SeqItemID AS  Code,ItemName AS  Name,mfctrShortName AS  Company," & _
            '             " ISNULL((select SUM(Quantity ) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID = mpItem.ItemID  ),0) AS  Stock," & _
            '             " 0 LocationCode,mpItem.minreorderlevel AS  ReorderQty ,ScheduleDrug AS  Schedule,ELStock AS  EmergStock " & _
            '             " FROM mpItem INNER JOIN  mManufacturer ON mManufacturer.mfctrID = mpItem.mfctrID " & _
            '             " ORDER BY "

            'SELECT P.[Id],P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,R.PricePerPack AS Rate 
            StrSql = "SELECT P.[Id] AS ItemID,P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,ISNULL(R.PricePerPack,1) AS Rate " & _
                         " FROM [NGSPaintsDB].[dbo].Product  P LEFT OUTER JOIN [NGSPaintsDB].[dbo].Supplier S ON(P.[SupplierId]=S.[Id]) LEFT OUTER JOIN [NGSPaintsDB].dbo.Rate R ON(P.ID=R.ProductId) " & _
                         " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].dbo.Product"


            If intColumnIndex = 1 Then
                StrSql = StrSql & " Code"
            Else : StrSql = StrSql & " Name"
            End If
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))

            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)

            'Call DbHelper.ExecuteDataReader(StrSql, Dtr)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function



    Public Shared Function GetItemListStock() As SqlDataReader
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  mpItem.ItemID,ISNULL((select SUM(Quantity ) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID = mpItem.ItemID  ),0) AS  Stock" & _
                         " FROM mpItem "
            Return DbHelper.ExecuteDataReader(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemListStock", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetListItemStock(ByVal ItemID As Long) As Integer
        'Dim StrSql As String
        Try
            'StrSql = "SELECT  ISNULL(SUM(Quantity),0) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID =" & ItemID
            Return DbHelper.ExecuteScalar("SELECT  ISNULL(SUM(Quantity),0) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID =" & ItemID)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetListItemStock")
            Return 0
        End Try
    End Function


    '@ on 20071130: Added Optional Parameter for getting Only Batch.
    Public Shared Function GetItemsBatch(ByVal ItemID As Integer, Optional ByVal OnlyBatch As Boolean = False) As DataTable
        Dim strSql As String = String.Empty

        Try

            If OnlyBatch = True And ItemID <> 0 Then
                strSql = " SELECT '' as CLRcode FROM tStock UNION SELECT CLRcode FROM tStock WHERE ItemID=" & ItemID & " ORDER BY CLRcode"
            ElseIf OnlyBatch = True And ItemID = 0 Then
                'strSql = "SELECT ItemID,CLRcode as Batch FROM tStock"
                strSql = " SELECT '' as CLRcode FROM tStock UNION SELECT CLRcode FROM tStock ORDER BY CLRcode"
            Else
                'USED ON SALE & SALE PRINTING FORM TO DISPLAY BATCHES OF CURRENT ITEM   -Chandrakant
                'strSql = "SELECT CLRcode as Batch,CAST(month(ExpDate)as VARCHAR) + '/' + RIGHT(CAST(YEAR(ExpDate)AS VARCHAR),2) as Expiry, " & _
                '         "MRP ,SUM(Quantity) as Stock,SalePrice1,SalePrice2,PurchasePrice,ConversionFactor FROM tStock " & _
                '         "WHERE(Quantity > 0 And ItemID = " & ItemID & ")" & _
                '         "GROUP BY ItemID ,CLRcode ,ExpDate,MRP,SalePrice1,SalePrice2,PurchasePrice,ConversionFactor "
                strSql = " SELECT CLRcode as Batch FROM tStock " & _
                         " WHERE(Quantity > 0 And ItemID = " & ItemID & ")" & _
                         " GROUP BY ItemID ,CLRcode "
            End If
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemsBatch", strSql)
            Return Nothing
        End Try
    End Function


    'code start                                 ....12/03/2007
    Public Shared Function GetmfctrItems(ByVal manufacturerID As String, ByVal vendorID As String) As DataTable
        Dim strsql As String = ""

        Try
            Dim Rel_Type As String = DbHelper.ExecuteScalar("SELECT DISTINCT Rel_type FROM trelation WHERE mfctrID='" & manufacturerID & "' AND vendorID='" & vendorID & "'")
            If Rel_Type = "0" Then  'All the items of that manufacturer are supllied by Vendor
                strsql = "SELECT mpitem.itemID,ItemName FROM mpitem where itemID='0'"
            Else
                strsql = " SELECT mpItem.ItemID,ItemName FROM mpitem,mmanufacturer" & _
                         " WHERE mpitem.mfctrID=mmanufacturer.mfctrID AND mpitem.mfctrID='" & manufacturerID & "'" & _
                         " AND mpitem.itemID NOT IN(SELECT mpItem.ItemID FROM mpItem,tRelation" & _
                         " WHERE tRelation.vendorID='" & vendorID & "' AND tRelation.mfctrID='" & manufacturerID & "' AND " & _
                         " tRelation.ItemID=mpItem.ItemID)"
            End If

            Return DbHelper.ExecuteDataTable(strsql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetmfctrItems", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetvendorItems(ByVal manufacturerID As String, ByVal vendorID As String) As DataTable
        Dim strsql As String = ""
        Try
            Dim Rel_Type As String = DbHelper.ExecuteScalar("SELECT DISTINCT Rel_type FROM trelation WHERE mfctrID='" & manufacturerID & "' AND vendorID='" & vendorID & "'")
            If Rel_Type = "0" Then 'All the items of that manufacturer are supllied by Vendor
                strsql = " SELECT mpitem.itemID,ItemName from mpitem,trelation,mmanufacturer" & _
                         " WHERE mpitem.mfctrID = mManufacturer.mfctrID " & _
                         " AND trelation.mfctrID=mmanufacturer.mfctrID " & _
                         " AND tRelation.vendorID='" & vendorID & "' AND tRelation.mfctrID='" & manufacturerID & "'"
            Else
                'Rel_Type = 1 Specific items of that manufacturer are supllied by Vendor
                strsql = " SELECT ItemID,ItemName FROM mpItem " & _
                         " WHERE ItemID IN(SELECT mpItem.ItemID FROM mpItem,tRelation " & _
                         " WHERE tRelation.vendorID='" & vendorID & "' AND tRelation.mfctrID='" & manufacturerID & "' AND " & _
                         " tRelation.ItemID=mpItem.ItemID)"
            End If
            Return DbHelper.ExecuteDataTable(strsql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetvendorItems", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetValidVendor(ByVal VendorID As Integer, ByVal ItemID As Integer) As DataTable
        Dim strsql As String = ""
        Try
            strsql = "SELECT mPItem.ItemID,ItemName from mPItem,tRelation" & _
                     " WHERE mPItem.ItemID = tRelation.ItemID" & _
                     " AND tRelation.VendorID= " & VendorID & _
                     " AND tRelation.ItemID= " & ItemID

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetvendorItems", strsql)
            Return Nothing
        End Try
    End Function
    'Check whether Item exists or not       27/10/2007
    Public Shared Function GetItemDtls(ByVal ItemID As Integer, ByVal VendorID As Integer, ByVal All As Boolean) As DataTable
        Dim strsql As String = ""
        Try
            If All = True Then
                'strsql = "SELECT Id AS ItemID FROM [NGSPaintsDB].[dbo].[Product] WHERE ProductCode='" & ItemSeqID & "' "
                strsql = "SELECT Id AS  ItemID,ProductCode AS ItemCode,ProductName AS Name,'' AS Company," & _
                         " '' AS Pack " & _
                         " FROM  [NGSPaintsDB].[dbo].[Product] " & _
                         " WHERE Id=" & ItemID & " ORDER BY ProductName "
                '" PurPack  + ' of '+ CAST(ItemsPerPack as varchar) +' '+  ItemForm as Pack " & _
                'Else
                '    strsql = " SELECT ItemID,SeqItemID,ItemName AS Name,mfctrShortName AS Company," & _
                '             " PurPack  + ' of '+ CAST(ItemsPerPack as varchar) +' '+  ItemForm as Pack  FROM mpitem , mManufacturer " & _
                '             " WHERE itemID in(SELECT ItemID FROM tRelation " & _
                '             " WHERE VendorID=" & VendorID & ") AND mManufacturer.mfctrID = mpItem.mfctrID AND ItemID=" & ItemID & "ORDER BY ItemName"

            End If

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemDtls", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function SaveItemMV(ByVal manufacturerID As String, ByVal vendorID As String, ByVal SelItem() As String, Optional ByVal AllItems As Boolean = False) As Boolean

        Dim Strsql As String = ""
        Dim count As Integer
        Dim dbCon As New dbAccess
        Dim param(3) As SqlParameter

        Try
            dbCon.BeginTransaction()
            Strsql = "DELETE FROM tRelation WHERE VendorID='" & vendorID & "' and mfctrID='" & manufacturerID & "'"
            dbCon.ExecuteNonQuery(Strsql)
            Strsql = "INSERT INTO tRelation (VendorID,mfctrID,Rel_type,ItemID) VALUES(@VendorID,@mfctrID,@Rel_type,@ItemID)"
            If AllItems = True Then
                param(0) = New SqlParameter("@VendorID", vendorID)
                param(1) = New SqlParameter("@mfctrID", manufacturerID)
                param(2) = New SqlParameter("@Rel_type", 0)
                param(3) = New SqlParameter("@ItemID", 0)
                dbCon.ExecuteNonQuery(Strsql, param)

            Else
                For count = 0 To SelItem.Length - 1
                    param(0) = New SqlParameter("@VendorID", vendorID)
                    param(1) = New SqlParameter("@mfctrID", manufacturerID)
                    param(2) = New SqlParameter("@Rel_type", 1)
                    param(3) = New SqlParameter("@ItemID", CInt(SelItem(count)))
                    dbCon.ExecuteNonQuery(Strsql, param)
                Next
            End If
            dbCon.CommitTransaction()
            Return True

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveItemMV", Strsql)
            dbCon.RollBackTransaction()
            Return False
        End Try
    End Function

    Public Shared Function SaveItemMV(ByVal manufacturerID As String, ByVal vendorID As String, ByVal SelItem() As String, ByVal dbCon As dbAccess, Optional ByVal AllItems As Boolean = False) As Boolean

        Dim Strsql As String = ""
        Dim count As Integer
        Dim param(3) As SqlParameter

        Try

            Strsql = "DELETE FROM tRelation WHERE VendorID='" & vendorID & "' and mfctrID='" & manufacturerID & "'"
            dbCon.ExecuteNonQuery(Strsql)
            Strsql = "INSERT INTO tRelation (VendorID,mfctrID,Rel_type,ItemID) VALUES(@VendorID,@mfctrID,@Rel_type,@ItemID)"
            If AllItems = True Then
                param(0) = New SqlParameter("@VendorID", vendorID)
                param(1) = New SqlParameter("@mfctrID", manufacturerID)
                param(2) = New SqlParameter("@Rel_type", 0)
                param(3) = New SqlParameter("@ItemID", 0)
                dbCon.ExecuteNonQuery(Strsql, param)

            Else
                For count = 0 To SelItem.Length - 1
                    param(0) = New SqlParameter("@VendorID", vendorID)
                    param(1) = New SqlParameter("@mfctrID", manufacturerID)
                    param(2) = New SqlParameter("@Rel_type", 1)
                    param(3) = New SqlParameter("@ItemID", CInt(SelItem(count)))
                    dbCon.ExecuteNonQuery(Strsql, param)
                Next
            End If

            Return True

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveItemMV1", Strsql)
            Return False
        End Try
    End Function

    Public Shared Function SaveItemsMV(ByVal manufacturerID As Integer, ByVal vendorID As Integer, ByVal ItemID As Integer) As Integer
        Dim Strsql As String = ""

        Dim param(2) As SqlParameter
        Try
            Strsql = "SELECT ItemID FROM tRelation WHERE VendorID=@VendorID " & _
                     " AND ItemID=@ItemID AND mfctrID=@mfctrID"
            param(2) = New SqlParameter("@VendorID", vendorID)
            param(1) = New SqlParameter("@mfctrID", manufacturerID)

            param(0) = New SqlParameter("@ItemID", ItemID)

            If DbHelper.ExecuteScalar(Strsql, param) > 0 Then
                Return 1
            Else
                Dim param1(3) As SqlParameter
                Strsql = "INSERT INTO tRelation (VendorID,mfctrID,Rel_type,ItemID) VALUES(@VendorID,@mfctrID,@Rel_type,@ItemID)"
                param1(0) = New SqlParameter("@VendorID", vendorID)
                param1(1) = New SqlParameter("@mfctrID", manufacturerID)


                param1(2) = New SqlParameter("@Rel_type", 1)
                param1(3) = New SqlParameter("@ItemID", ItemID)
                Return DbHelper.ExecuteNonQuery(Strsql, param1)
                'Return DbHelper.ExecuteScalar(Strsql, param1)
            End If

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveItemMV", Strsql)
            Return 0
        End Try

    End Function


    Public Shared Function GetConversionFactor(ByVal ItemID As Integer) As Integer
        'Returns Items per pack if sellLoose is true else 1
        Dim strSql As String = ""
        Try
            strSql = "SELECT SellLoose,ItemsPerPack FROM mpItem WHERE ItemID=" & ItemID

            Dim DtDItem As DataTable = DbHelper.ExecuteDataTable(strSql)
            If DtDItem.Rows(0).Item(0) = True Then
                Return DtDItem.Rows(0).Item(1)
            Else : Return 1
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetConversionFactor", strSql)
            Return 1
        End Try
    End Function

    '    Public Shared Function GetBillIetms(ByVal BillNo As Integer) As DataTable

    '@15/04/2008 : Query modified for Expired & Breakage Items for showing Strip Details according to the selected Application Customize Setting
    Public Shared Function GetItemsToExpire() As DataTable
        Dim StrSql As String = ""
        Try
            Select Case ConfigurationManager.AppSettings("StripDetails")
                Case "Expired"
                    StrSql = " SELECT tStock.CLRcode," & _
                             " CAST(MONTH(tStock.ExpDate)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(tStock.ExpDate)AS VARCHAR),2) AS EXPDATE," & _
                             " tStock.Quantity, mPItem.SeqItemID, mPItem.ItemShortName " & _
                             " FROM mPItem, tStock WHERE(mPItem.ItemID = tStock.ItemID And tStock.Quantity > 0)" & _
                             " AND DateDiff (D,getdate(),tStock.ExpDate) <= 0"

                Case "Near To Expiry"
                    StrSql = " SELECT tStock.CLRcode," & _
                             " CAST(MONTH(tStock.ExpDate)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(tStock.ExpDate)AS VARCHAR),2) AS EXPDATE," & _
                             " tStock.Quantity, mPItem.SeqItemID,mPItem.ItemShortName " & _
                             " FROM mPItem, tStock" & _
                             " WHERE mPItem.ItemID = tStock.ItemID AND tStock.Quantity > 0" & _
                             " AND DateDiff (D,getdate(),tStock.ExpDate)  between 0 and " & ConfigurationManager.AppSettings("NearToExpiry")

                Case "Breakage Item"
                    StrSql = " SELECT tStock.CLRcode,CAST(MONTH(tBreakage.BreakageDate)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(tBreakage.BreakageDate)AS VARCHAR),2) AS BreakageDate," & _
                             " tBreakage.Qty, mPItem.SeqItemID, mPItem.ItemShortName " & _
                             " FROM mPItem, tStock, tBreakage" & _
                             " WHERE mPItem.ItemID = tStock.ItemID AND tstock.StockID = tBreakage.StockID AND tBreakage.Qty > 0"
            End Select
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemsToExpire", StrSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetmfctrItems(ByVal manufacturerID As String) As DataTable
        Dim strsql As String = ""
        Try
            strsql = "SELECT ItemID,ItemName FROM mpItem" & _
                     " WHERE mfctrID = '" & manufacturerID & "' "

            Return DbHelper.ExecuteDataTable(strsql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetmfctrItems", strsql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetvendorItems(ByVal VendorID As Integer) As DataTable
        Dim strsql As String = ""
        Try
            'strsql = "SELECT ItemID,SeqItemID,ItemName AS Name FROM mpitem " & _
            '         " WHERE itemID in(SELECT ItemID  FROM tRelation WHERE VendorID= " & VendorID & ")  ORDER BY ItemName"

            'Comment Following Query and Write New one

            ''strsql = " SELECT ItemID,SeqItemID,ItemName AS Name,mfctrShortName AS Company," & _
            ''         " PurPack  + ' of '+ CAST(ItemsPerPack as varchar) +' '+  ItemForm as Pack  FROM mpitem , mManufacturer " & _
            ''         " WHERE itemID in(SELECT ItemID FROM tRelation " & _
            ''         " WHERE VendorID=" & VendorID & ") AND mManufacturer.mfctrID = mpItem.mfctrID ORDER BY ItemName"
            strsql = " SELECT ItemID, ItemName AS Name " & _
                     " FROM mpitem , mManufacturer " & _
                     " WHERE itemID in(SELECT ItemID FROM tRelation " & _
                     " WHERE VendorID=" & VendorID & ") AND mManufacturer.mfctrID = mpItem.mfctrID ORDER BY ItemName"
            Return DbHelper.ExecuteDataTable(strsql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetmfctrItems", strsql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetvendorItem(ByVal VendorID As Integer) As DataTable
        Dim strsql As String = ""
        Try
            strsql = "SELECT *  FROM tRelation" & _
                     " WHERE VendorID=" & VendorID & " order by mfctrID"
            Return DbHelper.ExecuteDataTable(strsql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetmfctrItems", strsql)
            Return Nothing
        End Try
    End Function
    'Code End 


    Public Shared Function GetSchemeID(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT SchemeID,schemeItemQty FROM tstock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'"
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetSchemeID", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetBatchItemStock(ByVal ItemID As Integer, ByVal CLRcode As String) As Integer
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT SUM(Quantity) FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'"

            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "IsItemExist", StrSql)
            Return Nothing
        End Try

    End Function

    '*****************************************************
    'Checked the Item (Exist or Not)
    'Modified By:- (20071106)
    '****************************
    Public Shared Function IsItemExist(ByVal strProductCode As String) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT mpItem.ItemID,SeqItemID AS ItemCode,ItemName AS Name,mfctrShortName AS Company,CurrentStock AS Stock," & _
                     " LocationCode,mpItem.minreorderlevel as ReorderQty  " & _
                     " FROM mpItem, mManufacturer, tStockSummary,mLocation " & _
                     " WHERE mManufacturer.mfctrID = mpItem.mfctrID And tStockSummary.ItemID = mpItem.ItemID" & _
                     " AND mLocation.LocationID=mpItem.LocationID and SeqItemID='" & strProductCode & "'"

            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "IsItemExist", StrSql)
            Return Nothing
        End Try

    End Function


    Public Shared Function GetItemStock(ByVal ItemID As Integer, Optional ByVal CLRcode As String = "") As Integer

        Dim StrSql As String = ""
        Try
            If CLRcode = "" Then
                StrSql = "SELECT CurrentStock AS Stock from tStockSummary WHERE ItemID =" & ItemID
            Else
                StrSql = "SELECT Quantity AS Stock from tStock WHERE ItemID =" & ItemID & " AND CLRcode='" & CLRcode & " '"
            End If
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemStock", StrSql)
            Return 0
        End Try
    End Function

    Public Shared Function GetWorkingStock(ByVal WorkingSeqID As Integer) As Integer
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT Qty AS Quantity FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetWorkingStock", StrSql)
            Return 0
        End Try
    End Function

    Public Shared Function GetWarningMessage(ByVal WarnModule As String, ByVal WarnBase As String, ByVal WarnValue As String) As String
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT warnMessage FROM mWarning WHERE WarnModule='" & WarnModule & "' AND warnBase='" & WarnBase & "' AND warnValue='" & WarnValue & "'"
            Dim dtb As New DataTable
            dtb = DbHelper.ExecuteDataTable(StrSql)
            If dtb.Rows.Count = 0 OrElse IsNothing(dtb.Rows(0).Item(0)) Then
                Return ""
            Else : Return dtb.Rows(0).Item(0)
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetWarningMessage", StrSql)
            Return ""
        End Try
    End Function

    '@20071122 : Add new function to valIDate Item Name
    Public Shared Function GetItemName() As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = " SELECT ItemName FROM MPITEM"
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemName", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetItemName(ByVal ItemID As Integer) As String
        Dim StrSql As String = ""
        Try
            StrSql = " SELECT ItemName FROM MPITEM " & _
                        "WHERE ItemID = " & ItemID & " and Status = 'A'"
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemName", StrSql)
            Return Nothing
        End Try
    End Function
    '@ on 20071210:Added for AvoID Item Name Duplication. 
    Public Shared Function GetItemByID(ByVal ItemID As Integer) As DataTable
        Dim strSql As String = ""

        Try
            strSql = "SELECT ItemID,ItemName " & _
                        "FROM mPItem " & _
                        "WHERE ItemID <> " & ItemID & " and Status = 'A'"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemByID", strSql)
            Return Nothing
        End Try
    End Function

    '@ on 20071214:Added for Getting Items By Batch Number
    Public Shared Function GetItemByBatch(ByVal CLRcode As String) As DataTable
        Dim strsql As String = ""
        Try
            If CLRcode <> "0" Then
                strsql = "SELECT 0 AS ItemID, '' AS ItemName FROM mPitem UNION " & _
                         " SELECT DISTINCT(tstock.ItemID), mPItem.ItemName " & _
                         " FROM tStock, mPItem WHERE CLRcode='" & CLRcode & "' " & _
                         " AND tStock.ItemID=mPItem.ItemID ORDER BY mPItem.ItemName"
            Else
                strsql = "SELECT 0 AS ItemID, '' AS ItemName FROM mPItem " & _
                         " UNION SELECT ItemID, ItemName  FROM mPItem " & _
                         " ORDER BY ItemName"
            End If

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemByBatch", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetItemInfo(ByVal ItemID As Integer, Optional ByVal CLRcode As String = "") As DataTable
        Dim strSql As String = ""
        Try
            If CLRcode = "" Then
                strSql = "SELECT TS.Currentstock AS 'Total Quantity', I.minReorderLevel AS 'ReOrder Level', I.Remark, " & _
                         "M.CodeDesc AS 'Schedule' " & _
                         "FROM tstocksummary TS  " & _
                         "INNER JOIN mPitem I ON TS.ItemID = I.ItemID " & _
                         "LEFT OUTER JOIN mMisccd M ON I.scheduledrug = M.codeid AND M.CodeType = 'SCHEDULETYPE' " & _
                         "WHERE  I.ItemID=" & ItemID
            Else
                Dim strRate As String = String.Empty
                Select Case ConfigurationManager.AppSettings("SaleRate")
                    Case "MRP"
                        strRate = "MRP"
                    Case "SALE1"
                        strRate = "SalePrice1"
                    Case "SALE2"
                        strRate = "SalePrice2"
                End Select

                'strSql = "SELECT TS.Currentstock AS 'Total Stock', I.minReorderLevel AS 'ReOrder Level', I.Remark as 'Note', I.ScheduleDrug, " & _
                '         "M.CodeDesc AS 'Schedule', T.PurchasePrice/T.Conversionfactor AS 'Purchase Price', T.Quantity, " & _
                '         "case T.PurchasePrice WHEN 0 THEN 0  ELSE (" & strRate & " - T.PurchasePrice)/T.Conversionfactor END  AS Profit, " & _
                '           strRate & "/T.Conversionfactor  AS SalePrice, T.PurchasePrice/T.Conversionfactor AS 'Purchase Price' " & _
                '         " FROM tStockSummary TS  " & _
                '         "INNER JOIN mPItem I ON TS.ItemID = I.ItemID " & _
                '         "LEFT OUTER JOIN tStock T ON TS.ItemID = T.ItemID  " & _
                '         "LEFT OUTER JOIN mMisccd M ON I.scheduledrug = M.CodeID AND M.CodeType = 'SCHEDULETYPE' " & _
                '         "AND I.ItemID = T.ItemID " & _
                '         "WHERE  I.ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'"

                strSql = "SELECT TS.Currentstock AS 'Total Stock',T.Quantity AS 'Batch Stock',M.CodeDesc AS 'Schedule', " & _
                         "I.minReorderLevel AS 'ReOrder Level', " & _
                         "case T.PurchasePrice when 0 then NULL else T.PurchasePrice/T.Conversionfactor END AS 'Purchase Price', " & _
                         strRate & "/T.Conversionfactor  AS 'SalePrice', " & _
                         "case T.PurchasePrice WHEN 0 THEN " & IIf(ConfigurationManager.AppSettings("ProfitPercent") = 0, 0, strRate & "/ T.Conversionfactor") & " ELSE (" & strRate & " - T.PurchasePrice)/T.Conversionfactor END AS 'Profit', " & _
                         "cast(I.ItemsPerPack as varchar(10)) + ' ' + N.CodeDesc  + '.' AS 'Pack', " & _
                         "I.sellloose AS 'Sell Loose',I.Remark AS 'Note',T.SchemeID AS 'Scheme On Batch' " & _
                         "FROM tStockSummary TS  " & _
                         "INNER JOIN mPItem I ON TS.ItemID = I.ItemID " & _
                         "LEFT OUTER JOIN tStock T ON TS.ItemID = T.ItemID  " & _
                         "LEFT OUTER JOIN mMisccd M ON I.scheduledrug = M.CodeID AND M.CodeType = 'SCHEDULETYPE'  " & _
                         "LEFT OUTER JOIN mMisccd N ON I.PurPack = N.CodeID AND N.CodeType = 'PURPACK'  " & _
                         "AND I.ItemID = T.ItemID WHERE  I.ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'"

            End If
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemInfo", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetItemID(ByVal ItemSeqID As String) As Integer
        Dim strsql As String = ""
        Try
            strsql = "SELECT Id AS ItemID FROM [NGSPaintsDB].[dbo].[Product] WHERE ProductCode='" & ItemSeqID & "' "
            'strsql = "SELECT ItemID FROM mPItem WHERE SeqItemID='" & ItemSeqID & "' "
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemID", strsql)
            Return Nothing
        End Try
    End Function


    'Written By:-(2008/02/13)
    'Getting ItemId based on ItemName
    Public Shared Function GetItemIDByItem(ByVal strItemName As String) As Integer
        Dim strsql As String = ""
        Try
            strsql = "SELECT ItemID FROM mPItem WHERE ItemName='" & Trim(strItemName) & "' "
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemID", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetStockQty(ByVal ItemID As Integer) As Integer
        Dim strsql As String = ""
        Try

            strsql = "SELECT CurrentStock FROM tStockSummary WHERE ItemID=" & ItemID
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetStockQty", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetStockQty(ByVal ItemID As Integer, ByVal CLRcode As String) As Integer
        Dim strsql As String = ""
        Try
            strsql = "SELECT Quantity FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "' "
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetStockQty1", strsql)
            Return Nothing
        End Try
    End Function

    Public Shared Function IsItemUsed(ByVal ItemID As Integer) As Integer
        Try
            If (DbHelper.ExecuteScalar("SELECT ItemID FROM tStock WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tSaleDet WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tStockRegister WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tPurchaseInvoicedet WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tDCdet WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tSaleReturndet WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT ItemID FROM tPurReturndet WHERE ItemID=" & ItemID)) > 0 Then
                Return 1
            Else
                Return 0
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "IsItemUsed")
            Return Nothing
        End Try
    End Function

    Public Shared Function DeleteItem(ByVal ItemID As Integer) As Integer
        Try
            Return DbHelper.ExecuteNonQuery(" DELETE FROM MpItem WHERE ItemID=" & ItemID & "")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "DeleteItem")
            Return Nothing
        End Try
    End Function


    Public Shared Function GetBillItems(ByVal BillNo As Integer, Optional ByVal ItemID As Integer = 0, Optional ByVal CLRcode As String = "", Optional ByVal Without As Boolean = False) As DataTable
        'Public Shared Function GetBillItems(ByVal BillNo As Integer, Optional ByVal ItemID As Integer = 0, Optional ByVal Without As Boolean = False) As DataTable

        Dim strSql As String = ""
        Try
            If Without = True Then
                ''strSql = " SELECT  DISTINCT (S.ItemID), I.SeqItemID AS Code ,I.ItemName AS Name " & _
                ''         " FROM mpItem I  INNER JOIN tStock S ON S.ItemID=I.ItemID WHERE DATEDIFF (D,GETDATE(),S.ExpDate) > 0 "
                strSql = " SELECT  DISTINCT (S.ItemID), I.SeqItemID AS Code ,I.ItemName AS Name " & _
                         " FROM mpItem I  INNER JOIN tStock S ON S.ItemID=I.ItemID"
                If ItemID <> 0 Then
                    strSql = strSql & " AND S.ItemID = " & ItemID & ""
                End If
                If CLRcode <> String.Empty Then
                    strSql = strSql & " AND S.CLRcode = '" & CLRcode & "'"
                End If
                strSql = strSql & " ORDER BY I.ItemName "
            Else

                'strSql = "SELECT DISTINCT( D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                '         " FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID " & _
                '         " WHERE DATEDIFF (D,GETDATE(),D.ExpDate) >0  ORDER BY I.ItemName "

                'IF ALL THE ITEMS OF CURRENT BILL IS RETUNED THEN THAT BILL WILL NOT LOAD ON THE FROM SO BILL HANDLING  SO FOR BILL ITEMS ARE NOT CHECKED HERE
                'intResult = DbHelper.ExecuteScalar("SELECT SaleReturnID FROM tsalereturn WHERE SaleID=" & BillNo)

                If DbHelper.ExecuteScalar("SELECT SaleReturnID FROM tsalereturn WHERE SaleID=" & BillNo) > 0 Then
                    ''strSql = "SELECT DISTINCT( D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                    ''         "FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID " & _
                    ''         "LEFT OUTER JOIN tSaleReturn ON tsalereturn.SaleID=" & BillNo & _
                    ''         "LEFT OUTER JOIN  tSaleReturnDet ON (tSaleReturnDet.SaleReturnID=tSaleReturn.SaleReturnID " & _
                    ''         "AND tSaleDet.ItemID=tsalereturndet.ItemID)" & _
                    ''         " WHERE( DATEDIFF (D,GETDATE(),D.ExpDate) > 0  AND tSaleDet.SaleID =" & BillNo & ") " & _
                    ''         "GROUP BY mpItem.ItemID, SeqItemID ,ItemName ,tSaleDet.CLRcode,tSaleDet.SalePrice,tSaleDet.Qty " & _
                    ''         "HAVING CAST(tSaleDet.Qty AS INTEGER)- ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) > 0 ORDER BY I.ItemName "

                    'strSql = "SELECT DISTINCT( D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                    '         "FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID " & _
                    '         "LEFT OUTER JOIN tSaleReturn ON tsalereturn.SaleID=" & BillNo & _
                    '         "LEFT OUTER JOIN  tSaleReturnDet ON (tSaleReturnDet.SaleReturnID=tSaleReturn.SaleReturnID " & _
                    '         "AND tSaleDet.ItemID=tsalereturndet.ItemID)" & _
                    '         " WHERE(tSaleDet.SaleID =" & BillNo & ") " & _
                    '         "GROUP BY mpItem.ItemID, SeqItemID ,ItemName ,tSaleDet.CLRcode,tSaleDet.SalePrice,tSaleDet.Qty " & _
                    '         "HAVING CAST(tSaleDet.Qty AS INTEGER)- ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) > 0 ORDER BY I.ItemName "

                    strSql = "SELECT DISTINCT(D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                             " FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID" & _
                             " LEFT OUTER JOIN tSaleReturn ON tsalereturn.SaleID=" & BillNo & _
                             " LEFT OUTER JOIN  tSaleReturnDet ON (tSaleReturnDet.SaleReturnID=tSaleReturn.SaleReturnID" & _
                             " AND D.ItemID=tsalereturndet.ItemID)" & _
                             " WHERE(D.SaleID =" & BillNo & ")" & _
                             " GROUP BY D.ItemID, SeqItemID ,ItemName ,D.CLRcode, D.SalePrice, D.Qty" & _
                             " HAVING CAST(D.Qty AS INTEGER)- ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) > 0 ORDER BY I.ItemName"
                Else

                    ''strSql = "SELECT DISTINCT( D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                    ''         " FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID " & _
                    ''         " WHERE DATEDIFF (D,GETDATE(),D.ExpDate) > 0 AND SaleID=" & BillNo & " "

                    strSql = "SELECT DISTINCT(D.ItemID), I.SeqItemID AS Code,I.ItemName as Name" & _
                             " FROM tSaleDet D INNER JOIN  mpItem I ON I.ItemID=D.ItemID " & _
                             " WHERE SaleID=" & BillNo & " "

                    strSql = strSql & " ORDER BY I.ItemName "
                End If
            End If
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetBillItems", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetBillIetmsBatch(ByVal BillNo As Integer, ByVal CLRcode As String, ByVal ItemID As Integer, Optional ByVal Without As Boolean = False) As DataTable
        Dim strSql As String = ""
        Dim intResult As Integer
        Try
            If Without Then
                Dim strSaleRate As String = ConfigurationManager.AppSettings("SaleRate")


                'strSql = " SELECT DISTINCT(S.CLRcode) AS CLRcode," & _
                '        " 1 as Quantity, AVG(S.MRP) as Rate," & _
                '        " '' AS Reason,1 AS Stock" & _
                '        " FROM tStock S" & _
                '        " INNER JOIN mpItem I ON (I.ItemID = S.ItemID) " & _
                '        " WHERE I.ItemID = " & ItemID & ""

                strSql = " SELECT DISTINCT(S.CLRcode) AS CLRcode, " & _
                       " 1 as Quantity, (" & strSaleRate & " / ConversionFactor) as Rate," & _
                       " '' AS Reason, 1 AS Stock " & _
                       " FROM tStock S " & _
                       " INNER JOIN mpItem I ON (I.ItemID = S.ItemID) " & _
                       " WHERE I.ItemID = " & ItemID & ""

                If Trim(CLRcode) <> "" Then
                    strSql = strSql & " AND  S.CLRcode = '" & CLRcode & "'"
                End If
                strSql = strSql & " GROUP BY S.CLRcode,ConversionFactor," & strSaleRate & ""



            Else
                intResult = DbHelper.ExecuteScalar("SELECT SaleReturnID FROM tsalereturn WHERE SaleID=" & BillNo)
                If intResult > 0 Then
                    strSql = "SELECT DISTINCT(D.CLRcode) ," & _
                             "CAST(D.Qty AS FLOAT)-ISNULL(SUM(CAST(RD.Qty AS INTEGER)),0) AS Quantity," & _
                             " D.SalePrice AS Rate," & _
                             "'' AS Reason,CAST(D.Qty AS FLOAT)-ISNULL(SUM(CAST(RD.Qty AS INTEGER)),0) AS Stock " & _
                             " FROM tSaleDet D LEFT OUTER JOIN tSaleReturn R ON R.SaleID=D.SaleID " & _
                             " LEFT OUTER JOIN  tSaleReturnDet RD ON (RD.SaleReturnID=R.SaleReturnID AND D.ItemID=RD.ItemID) " & _
                             " WHERE(D.SaleID =" & BillNo & "  AND D.ItemID= " & ItemID & IIf(CLRcode = "", "", " AND  D.CLRcode ='" & CLRcode & "'") & _
                             " ) GROUP BY D.CLRcode,D.SalePrice,D.Qty " & _
                             " HAVING CAST(D.Qty AS INTEGER)- ISNULL(SUM(CAST(RD.Qty AS INTEGER)),0) > 0 "
                Else
                    ''strSql = "SELECT DISTINCT (CLRcode)AS CLRcode," & _
                    ''         " SUM(CAST(Qty as FLOAT)) AS Quantity,AVG(D.SalePrice) AS Rate," & _
                    ''         " '' AS Reason ,SUM(CAST(Qty as FLOAT)) AS Stock" & _
                    ''         " FROM  tSaleDet D" & _
                    ''         " WHERE DATEDIFF (D,GETDATE(),D.ExpDate) >0 AND SaleID = " & BillNo & _
                    ''         " AND D.ItemID= " & ItemID & IIf(CLRcode = "", "", " AND  D.CLRcode =' " & CLRcode & "'") & _
                    ''         " GROUP BY CLRcode"
                    strSql = "SELECT DISTINCT (CLRcode)AS CLRcode," & _
                             " SUM(CAST(Qty as FLOAT)) AS Quantity,AVG(D.SalePrice) AS Rate," & _
                             " '' AS Reason ,SUM(CAST(Qty as FLOAT)) AS Stock" & _
                             " FROM  tSaleDet D" & _
                             " WHERE SaleID = " & BillNo & _
                             " AND D.ItemID= " & ItemID & IIf(CLRcode = "", "", " AND  D.CLRcode ='" & CLRcode & "'") & _
                             " GROUP BY CLRcode"
                End If
            End If
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetBillIetmsBatch", strSql)
            Return Nothing
        End Try

    End Function


    'Written By:-(2008/03/07)
    'Saving Excel Data for Item Master
    Public Shared Function SaveExcelItem(ByRef Item As CItem, ByVal SaveUpdate As String) As Integer
        Dim dbConnection As New dbAccess
        Dim intItemID As Integer
        Dim intResult As Integer
        Try
            dbConnection.BeginTransaction()
            intItemID = SaveItem(Item, SaveUpdate, dbConnection)
            intResult = intItemID
            If intResult = 0 Then Exit Function
            If SaveUpdate = "S" Then
                intResult = SaveStockSummary(Item, SaveUpdate, dbConnection)
            Else
                intResult = 1
            End If
        Catch ex As Exception
            intResult = 0
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "Save")
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                SaveExcelItem = 0
            Else
                dbConnection.CommitTransaction()
                SaveExcelItem = intItemID
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function



    'Public Shared Function GetItems(ByVal vendorID As Integer) As DataTable
    '    Dim StrSql As String = ""
    '    Try
    '        StrSql = "SELECT ItemName, tStock.ItemID, CodeDesc,mfctrName as Manufacturer ,SUM(Quantity) as Stock, mpItem.PurPack, SalePrice " & _
    '                 "FROM  mpItem,tStock,MMISCCD, mManufacturer " & _
    '                 "WHERE mPItem.ItemForm=MMISCCD.CodeID AND MMISCCD.CodeType='ITEMFORM' AND mpItem.itemID = tStock.itemID" & _
    '                 " AND  mPItem.mfctrID=mManufacturer.mfctrID AND tStock.itemID IN (SELECT itemID from tRelation where VendorID = " & vendorID & _
    '                 ") AND mPItem.Status = 'A' GROUP BY  tStock.ItemID,ItemName,CodeDesc,mfctrName,mpItem.PurPack,SalePrice "
    '        Return DbHelper.ExecuteDataTable(StrSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItems", StrSql)
    '        Return Nothing
    '    End Try
    'End Function

    '                   29/08/2007
    'Public Shared Function GetItems(ByVal items As String) As DataTable
    '    Dim StrSql As String = ""
    '    Try
    '        If items = "" Then
    '            StrSql = "SELECT ItemID,SeqItemID,ItemName AS Name FROM mPItem"
    '        Else
    '            StrSql = "SELECT ItemID,SeqItemID,ItemName AS Name FROM mpitem WHERE itemID in(" & items & ")"
    '        End If

    '        Return DbHelper.ExecuteDataTable(StrSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItems", StrSql)
    '        Return Nothing
    '    End Try
    'End Function
    'Public Shared Function GetBillIetms(ByVal BillNo As Integer, Optional ByVal CLRcode As String = "", Optional ByVal ItemID As Integer = 0, Optional ByVal Without As Boolean = False) As DataTable
    '    Dim strSql As String = ""
    '    Dim intResult As Integer
    '    Try
    '        If Without = True Then
    '            'strSql = " SELECT mpItem.ItemID, tstock.stockID AS Code, mpitem.ItemName as Name, tstock.batchNo AS CLRcode," & _
    '            '         " tstock.Quantity as Quantity, tstock.MRP as Rate," & _
    '            '         " CAST(tstock.Quantity as integer)*tstock.MRP as Amount, '' AS Reason, tstock.Quantity as Stock, tstock.VendorID as VendorID" & _
    '            '         " FROM Tstock " & _
    '            '         " INNER JOIN mpItem ON (mpItem.ItemID = tstock.ItemID)"

    '            'Temporary Change in the query
    '            'Concatinate ItemName and CLRcode in Name field
    '            'Modified By:-(2008/03/03)
    '            strSql = " SELECT mpItem.ItemID, tstock.stockID AS Code, mpitem.ItemName + '(' + tstock.batchNo + ')' as Name, tstock.batchNo AS CLRcode," & _
    '                     " 1 as Quantity, tstock.MRP as Rate," & _
    '                     " CAST(tstock.Quantity as float)*tstock.MRP as Amount, '' AS Reason, tstock.Quantity as Stock" & _
    '                     " FROM Tstock " & _
    '                     " INNER JOIN mpItem ON (mpItem.ItemID = tstock.ItemID)"

    '            If CLRcode <> "" Then
    '                strSql = strSql & " AND  tstock.CLRcode = '" & CLRcode & "'"
    '            End If
    '            If ItemID <> 0 Then
    '                strSql = strSql & " AND mpItem.ItemID = " & ItemID & ""
    '            End If
    '            'strSql = strSql & " GROUP BY mpItem.ItemID, SeqItemID,ItemName,CLRcode,tSaleDet.SalePrice"
    '        Else
    '            intResult = DbHelper.ExecuteScalar("SELECT SaleReturnID FROM tsalereturn WHERE SaleID=" & BillNo)
    '            If intResult > 0 Then
    '                'strSql = "SELECT mpItem.ItemID, SeqItemID AS Code,ItemName AS Name,tSaleDet.CLRcode," & _
    '                '         " CAST(tSaleDet.Qty AS INTEGER)-SUM(CAST(tSaleReturnDet.Qty AS INTEGER)) AS Quantity, " & _
    '                '         " tSaleDet.SalePrice AS Rate,(CAST(tSaleDet.Qty AS INTEGER)-SUM(CAST(tSaleReturnDet.Qty AS INTEGER))) *  tSaleDet.SalePrice AS Amount ,  " & _
    '                '         " '' AS Reason,CAST(tSaleDet.Qty AS INTEGER)-SUM(CAST(tSaleReturnDet.Qty AS INTEGER)) AS Stock " & _
    '                '         " FROM tSale, tSaleDet, tSaleReturn, mpItem, tSaleReturnDet " & _
    '                '         " where tSale.SaleID = tSaleDet.SaleID AND tSaleDet.SaleID=tsalereturn.SaleID " & _
    '                '         " AND tSaleReturnDet.SaleReturnID=tSaleReturn.SaleReturnID AND mpItem.ItemID=tsaledet.ItemID" & _
    '                '         " AND tSaleDet.ItemID=tsalereturndet.ItemID and tSale.SaleID=" & BillNo & _
    '                '         " GROUP BY mpItem.ItemID, SeqItemID ,ItemName ,tSaleDet.CLRcode,tSaleDet.SalePrice,tSaleDet.Qty" & _
    '                '         " HAVING CAST(tSaleDet.Qty AS INTEGER)- SUM(CAST(tSaleReturnDet.Qty AS INTEGER)) > 0 "

    '                'Temporary Change in the query
    '                'Concatinate ItemName and CLRcode in Name field
    '                'Modified By:-(2008/03/03)
    '                strSql = "SELECT mpItem.ItemID, SeqItemID AS Code,ItemName + '(' + tSaleDet.CLRcode + ')' as Name,tSaleDet.CLRcode," & _
    '                         "CAST(tSaleDet.Qty AS FLOAT)-ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) AS Quantity," & _
    '                         "tSaleDet.SalePrice AS Rate,(CAST(tSaleDet.Qty AS FLOAT)-ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0)) *  tSaleDet.SalePrice AS Amount," & _
    '                         "'' AS Reason,CAST(tSaleDet.Qty AS FLOAT)-ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) AS Stock " & _
    '                         "FROM tSaleDet INNER JOIN  mpItem ON mpItem.ItemID=tsaledet.ItemID " & _
    '                         "LEFT OUTER JOIN tSaleReturn ON tsalereturn.SaleID=" & BillNo & _
    '                         "LEFT OUTER JOIN  tSaleReturnDet ON (tSaleReturnDet.SaleReturnID=tSaleReturn.SaleReturnID " & _
    '                         "AND tSaleDet.ItemID=tsalereturndet.ItemID) Where(tSaleDet.SaleID =" & BillNo & ") " & _
    '                         "GROUP BY mpItem.ItemID, SeqItemID ,ItemName ,tSaleDet.CLRcode,tSaleDet.SalePrice,tSaleDet.Qty " & _
    '                         "HAVING CAST(tSaleDet.Qty AS INTEGER)- ISNULL(SUM(CAST(tSaleReturnDet.Qty AS INTEGER)),0) > 0 "
    '            Else
    '                strSql = "SELECT   mpItem.ItemID, SeqItemID AS Code,ItemName + '(' + CLRcode + ')' as Name, CLRcode," & _
    '                         " SUM(CAST(Qty as FLOAT)) AS Quantity,tSaleDet.SalePrice AS Rate,SUM(CAST(Qty as FLOAT))*tSaleDet.SalePrice AS Amount," & _
    '                         " '' AS Reason ,SUM(CAST(Qty as FLOAT)) AS Stock" & _
    '                         " FROM mpItem, tSaleDet " & _
    '                         " WHERE mpItem.ItemID = tSaleDet.ItemID And SaleID = " & BillNo & _
    '                       " GROUP BY mpItem.ItemID, SeqItemID,ItemName,CLRcode,tSaleDet.SalePrice"

    '            End If
    '        End If

    '        Return DbHelper.ExecuteDataTable(strSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetBillIetms", strSql)
    '        Return Nothing
    '    End Try

    'End Function

    'Public Shared Function SaveItem(ByVal Item As CItem) As Integer

    '    Dim sql As String = ""
    '    Dim param(34) As SqlParameter
    '    Try
    '        sql = "INSERT INTO mPItem(ItemName,ItemShortName,ItemType,ItemCatID,ItemForm,Grade,mfctrID,GenericName,ItemUnit," & _
    '                "PurPack,ItemsPerPack,PurUnit,LocationID,SellLoose,SellUnit,LastPurPrice,PurPrice,SalePrice," & _
    '                "Barcode,MaxDiscount,OpeningStock,MinQty,MaxQty,MinReorderLevel,MaxReorderQty,Action1,Action2," & _
    '                "Action3,VendorID1,VendorID2,VendorID3,ScheduleDrug,Remark,Status,LU,LD)" & _
    '                "VALUES(@ItemName,@ItemShortName,@ItemType,@ItemCatID,@ItemForm,@Grade,@mfctrID,@GenericName,@ItemUnit," & _
    '                "@PurPack,@ItemsPerPack,@PurUnit,@LocationID,@SellLoose,@SellUnit,@LastPurPrice,@PurPrice,@SalePrice," & _
    '                "@Barcode,@MaxDiscount,@OpeningStock,@MinQty,@MaxQty,@MinReorderLevel,@MaxReorderQty,@Action1,@Action2," & _
    '                "@Action3,@VendorID1,@VendorID2,@VendorID3,@ScheduleDrug,@Remark,@Status,@LU,@LD)"

    '        param(0) = New SqlParameter("@ItemName", Item.ItemName)
    '        param(1) = New SqlParameter("@ItemShortName", Item.ShortName)
    '        param(2) = New SqlParameter("@ItemType", Item.ItemType)
    '        param(3) = New SqlParameter("@ItemCatID,@ItemForm", Item.ItemCategoryID)
    '        param(4) = New SqlParameter("@Grade", Item.ItemGrade)
    '        param(5) = New SqlParameter("@mfctrID", Item.ManufacturedID)
    '        param(6) = New SqlParameter("@GenericName", Item.GenericName)
    '        param(7) = New SqlParameter("@ItemUnit", Item.ItemUnit)
    '        param(8) = New SqlParameter("@PurPack", Item.PurPackType)
    '        param(9) = New SqlParameter("@ItemsPerPack", Item.ItemsPerPack)
    '        param(10) = New SqlParameter("@PurUnit", Item.PurchaseUnit)
    '        param(11) = New SqlParameter("@LocationID", Item.LocationID)
    '        param(12) = New SqlParameter("@SellLoose", Item.SellInLoose)
    '        param(13) = New SqlParameter("@SellUnit", Item.SellUnit)
    '        param(14) = New SqlParameter("@LastPurPrice", Item.LastPurchasePrice)
    '        param(15) = New SqlParameter("@PurPrice", Item.PurchasePrice)
    '        param(16) = New SqlParameter("@SalePrice", Item.SalePrice)
    '        param(17) = New SqlParameter("@Barcode", Item.BarCode)
    '        param(18) = New SqlParameter("@MaxDiscount", Item.MaxDiscount)
    '        param(19) = New SqlParameter("@OpeningStock", Item.OpeningStock)
    '        param(20) = New SqlParameter("@MinQty", Item.MinQuantity)
    '        param(21) = New SqlParameter("@MaxQty", Item.MaxQuantity)
    '        param(22) = New SqlParameter("@MinReorderLevel", Item.MinReOrderLevel)
    '        param(23) = New SqlParameter("@MaxReorderQty", Item.MaxReOrderQty)
    '        param(24) = New SqlParameter("@Action1", Item.Action1)
    '        param(25) = New SqlParameter("@Action2", Item.Action2)
    '        param(26) = New SqlParameter("@Action3", Item.Action3)
    '        param(27) = New SqlParameter("@VendorID1", Item.VendorID1)
    '        param(28) = New SqlParameter("@VendorID2", Item.VendorID2)
    '        param(29) = New SqlParameter("@VendorID3", Item.VendorID3)
    '        param(30) = New SqlParameter("@ScheduleDrug", Item.Scheduletype)
    '        param(31) = New SqlParameter("@Remark", Item.Remark)
    '        param(32) = New SqlParameter("@Status", "A")
    '        param(33) = New SqlParameter("@LU", CCommon.GetUserID())
    '        param(34) = New SqlParameter("@LD", Date.Now())

    '        Return DbHelper.ExecuteNonQuery(sql, param)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "SaveItem", sql)
    '        Return Nothing
    '    End Try
    'End Function

    ' ...                   24/08/2007

End Class
